<?php
namespace App\Models\Scripts;

use App\Http\Config\ErrorCode;
use App\Library\Tools;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class va056868aaa78c4a94611bc6e45f50e00 extends Sysupdate
{
    protected $version_id ='VUE-CRM';

    private $returnData = array('code'=>0,'message'=>'执行成功');

    public function __construct()
    {
        parent::__construct();
    }

    public function upgrade()
    {
        $this->fixAdminData();
        if($this->returnData['code']>0){
            return $this->returnData;
        }
        $this->fixCaseData();
        if($this->returnData['code']>0){
            return $this->returnData;
        }
        $this->fixCustomerData();
        if($this->returnData['code']>0){
            return $this->returnData;
        }
        $this->fixMemberData();
        if($this->returnData['code']>0){
            return $this->returnData;
        }
        $this->fixMemberExtendData();
        if($this->returnData['code']>0){
            return $this->returnData;
        }
        $this->fixMemberSessionData();
        if($this->returnData['code']>0){
            return $this->returnData;
        }
        return $this->returnData;
    }

    /*增加admin_session表 */
    public function fixAdminData()
    {
        try {
            //检查是否完成
            $res = DB::table('admin_session')->get();
            if($res){
                $this->returnData['code'] = 101;
                $this->returnData['message'] = 'table admin_session exist';
                return $this->returnData;
            }
        } catch(\Illuminate\Database\QueryException $e) {
            $errorInfo = json_decode(json_encode($e->errorInfo),true);
            if($errorInfo[0]!='42S02'){
                Log::info('table admin_session error:',array('errorinfo'=>json_encode($e)));
                $this->returnData['code'] = 102;
                $this->returnData['message'] = json_encode($e);
                return $this->returnData;
            }
            $sql = "CREATE TABLE `admin_session` ( ".
                "  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id', ".
                "  `session_id` varchar(50) DEFAULT NULL, ".
                "  `code` varchar(250) DEFAULT NULL, ".
                "  `admin_id` int(11) DEFAULT NULL COMMENT '管理员ID', ".
                "  `login_ip` char(20) DEFAULT NULL COMMENT '登录IP', ".
                "  `expire_time` int(11) DEFAULT NULL COMMENT '过期时间', ".
                "  `create_time` int(11) DEFAULT NULL COMMENT '创建时间', ".
                "  PRIMARY KEY (`id`) ".
                ") DEFAULT CHARSET=utf8;";
            DB::statement($sql);
            return $this->returnData;
        }
        return $this->returnData;
    }

    /* 修复案例 */
    public function fixCaseData()
    {
        $sql = "ALTER TABLE cases CHANGE case_url case_pic varchar(80) DEFAULT NULL COMMENT '案例图片地址';";
        DB::statement($sql);
        $sql = "ALTER TABLE cases CHANGE case_edition case_version varchar(80) DEFAULT NULL COMMENT '案例版本';";
        DB::statement($sql);
        $sql = "ALTER TABLE cases CHANGE industry type tinyint(4) DEFAULT NULL COMMENT '行业';";
        DB::statement($sql);
        return $this->returnData;
    }

    /* 增加线索表 */
    public function fixCustomerData()
    {
        try {
            //检查是否完成
            $res = DB::table('customer')->get();
            if($res){
                $this->returnData['code'] = 101;
                $this->returnData['message'] = 'table customer exist';
                return $this->returnData;
            }
        } catch(\Illuminate\Database\QueryException $e) {
            $errorInfo = json_decode(json_encode($e->errorInfo),true);
            if($errorInfo[0]!='42S02'){
                Log::info('table customer error:',array('errorinfo'=>json_encode($e)));
                $this->returnData['code'] = 102;
                $this->returnData['message'] = json_encode($e);
                return $this->returnData;
            }
            $sql = "CREATE TABLE `customer` ( ".
                "  `id` int(11) NOT NULL AUTO_INCREMENT, ".
                "  `name` varchar(30) DEFAULT NULL COMMENT '用户名', ".
                "  `realname` varchar(30) DEFAULT NULL COMMENT '真实名称', ".
                "  `type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '类型 0为个人 1为企业', ".
                "  `mobile` varchar(20) DEFAULT NULL COMMENT '手机号码', ".
                "  `recommend` int(10) NOT NULL DEFAULT '1' COMMENT '推荐人ID', ".
                "  `addperson` varchar(30) NOT NULL DEFAULT 'root' COMMENT '上传者', ".
                "  `email` varchar(40) DEFAULT NULL COMMENT '邮箱', ".
                "  `position` varchar(30) DEFAULT NULL COMMENT '职位', ".
                "  `company` varchar(50) DEFAULT NULL COMMENT '公司', ".
                "  `wechat` varchar(20) DEFAULT NULL COMMENT '微信', ".
                "  `qq` varchar(15) DEFAULT NULL COMMENT 'QQ', ".
                "  `contact_next_time` timestamp NULL DEFAULT NULL COMMENT '下次联系时间', ".
                "  `source` varchar(35) DEFAULT NULL COMMENT '用户来源', ".
                "  `project` varchar(60) DEFAULT NULL COMMENT '项目', ".
                "  `progress` varchar(30) DEFAULT '初步接触' COMMENT '客户跟进状态', ".
                "  `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '用户状态 0为冻结 1为正常', ".
                "  `remarks` varchar(255) DEFAULT NULL COMMENT '备注', ".
                "  `cust_state` tinyint(1) DEFAULT '0' COMMENT '0 未激活/1 已激活', ".
                "  `created_at` timestamp NULL DEFAULT NULL, ".
                "  `updated_at` timestamp NULL DEFAULT NULL, ".
                "  PRIMARY KEY (`id`) ".
                ") DEFAULT CHARSET=utf8; ";
            DB::statement($sql);
            $this->_copyCustomerData();
            return $this->returnData;
        }
        return $this->returnData;
    }

    /* 增加客户相关表 */
    public function fixMemberData()
    {
        try {
            //检查是否完成
            $res = DB::table('member')->get();
            if($res){
                $this->returnData['code'] = 101;
                $this->returnData['message'] = 'table member exist';
                return $this->returnData;
            }
        } catch(\Illuminate\Database\QueryException $e) {
            $errorInfo = json_decode(json_encode($e->errorInfo),true);
            if($errorInfo[0]!='42S02'){
                Log::info('table member error:',array('errorinfo'=>json_encode($e)));
                $this->returnData['code'] = 102;
                $this->returnData['message'] = json_encode($e);
                return $this->returnData;
            }
            $sql = "CREATE TABLE `member` ( ".
                "  `id` int(11) NOT NULL AUTO_INCREMENT, ".
                "  `name` varchar(30) DEFAULT NULL COMMENT '用户名', ".
                "  `mobile` varchar(20) DEFAULT NULL COMMENT '手机号码', ".
                "  `password` varchar(64) DEFAULT NULL COMMENT '密码', ".
                "  `email` varchar(40) DEFAULT NULL COMMENT '邮箱', ".
                "  `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '用户状态 0为冻结 1为正常', ".
                "  `openid` varchar(50) DEFAULT NULL COMMENT '客户openid', ".
                "  `active_time` timestamp NULL DEFAULT NULL COMMENT '激活时间', ".
                "  `create_time` timestamp NULL DEFAULT NULL, ".
                "  `update_time` timestamp NULL DEFAULT NULL, ".
                "  PRIMARY KEY (`id`) ".
                ") ENGINE=InnoDB AUTO_INCREMENT=4799 DEFAULT CHARSET=utf8;";
            DB::statement($sql);
            $this->_copyMemberData();
            return $this->returnData;
        }
        return $this->returnData;
    }

    /* 增加客户扩展表 */
    public function fixMemberExtendData()
    {
        try {
            //检查是否完成
            $res = DB::table('member_extend')->get();
            if($res){
                $this->returnData['code'] = 101;
                $this->returnData['message'] = 'table member_extend exist';
                return $this->returnData;
            }
        } catch(\Illuminate\Database\QueryException $e) {
            $errorInfo = json_decode(json_encode($e->errorInfo),true);
            if($errorInfo[0]!='42S02'){
                Log::info('table member_extend error:',array('errorinfo'=>json_encode($e)));
                $this->returnData['code'] = 102;
                $this->returnData['message'] = json_encode($e);
                return $this->returnData;
            }
            $sql = "CREATE TABLE `member_extend` ( ".
                "  `member_id` int(11) NOT NULL COMMENT 'member表 客户ID', ".
                "  `realname` varchar(30) DEFAULT NULL COMMENT '真实名称', ".
                "  `type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '类型 0为个人 1为企业', ".
                "  `avatar` varchar(200) DEFAULT NULL COMMENT '头像地址', ".
                "  `level` tinyint(2) NOT NULL DEFAULT '5' COMMENT '默认等级', ".
                "  `recommend` int(11) NOT NULL DEFAULT '1' COMMENT '推荐人ID', ".
                "  `addperson` varchar(30) NOT NULL DEFAULT 'root' COMMENT '上传者', ".
                "  `cash_coupon` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '赠送金', ".
                "  `balance` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '余额', ".
                "  `position` varchar(30) DEFAULT NULL COMMENT '职位', ".
                "  `company` varchar(50) DEFAULT NULL COMMENT '公司', ".
                "  `wechat` varchar(20) DEFAULT NULL COMMENT '微信', ".
                "  `qq` varchar(15) DEFAULT NULL COMMENT 'QQ', ".
                "  `source` varchar(35) DEFAULT NULL COMMENT '用户来源', ".
                "  `project` varchar(60) DEFAULT NULL COMMENT '项目', ".
                "  `remarks` varchar(255) DEFAULT NULL COMMENT '备注', ".
                "  `certify_name` varchar(255) DEFAULT NULL COMMENT '身份证名称/企业名称', ".
                "  `certify_no` varchar(18) DEFAULT NULL COMMENT '身份证号码/营业执照', ".
                "  `certify_pic` text COMMENT '身份证正面/营业执照', ".
                "  `update_time` timestamp NULL DEFAULT NULL COMMENT '修改时间', ".
                "  PRIMARY KEY (`member_id`), ".
                "  UNIQUE KEY `member_id` (`member_id`) USING BTREE ".
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8; ";
            DB::statement($sql);
            $this->_copyMemberExtendData();
            return $this->returnData;
        }
        return $this->returnData;
    }

    /* 增加客户session表 */
    public function fixMemberSessionData()
    {
        try {
            //检查是否完成
            $res = DB::table('member_session')->get();
            if($res){
                $this->returnData['code'] = 101;
                $this->returnData['message'] = 'table member_session exist';
                return $this->returnData;
            }
        } catch(\Illuminate\Database\QueryException $e) {
            $errorInfo = json_decode(json_encode($e->errorInfo),true);
            if($errorInfo[0]!='42S02'){
                Log::info('table member_session error:',array('errorinfo'=>json_encode($e)));
                $this->returnData['code'] = 102;
                $this->returnData['message'] = json_encode($e);
                return $this->returnData;
            }
            $sql = "CREATE TABLE `member_session` ( ".
                "  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id', ".
                "  `session_id` varchar(50) DEFAULT NULL, ".
                "  `code` varchar(250) DEFAULT NULL, ".
                "  `member_id` int(11) DEFAULT NULL COMMENT '管理员ID', ".
                "  `login_ip` char(20) DEFAULT NULL COMMENT '登录IP', ".
                "  `expire_time` int(11) DEFAULT NULL COMMENT '过期时间', ".
                "  `create_time` int(11) DEFAULT NULL COMMENT '创建时间', ".
                "  PRIMARY KEY (`id`) ".
                ") DEFAULT CHARSET=utf8; ";
            DB::statement($sql);
            return $this->returnData;
        }
        return $this->returnData;
    }

    /* 修复客户数据表 */
    private function _copyCustomerData()
    {
        $res = DB::table('members')->select('id','name','realname','type','mobile','recommend','addperson','email','position','company','wechat','qq','nextcontact','source','project','customer_level','status','remarks','cust_state','created_at','updated_at')->get();
        if(!$res){
            $this->returnData = ErrorCode::$admin_enum['not_exist'];
            return $this->returnData;
        }
        $res = json_decode(json_encode($res),true);
        if(!is_array($res) || count($res)<1){
            $this->returnData = ErrorCode::$admin_enum['not_exist'];
            return $this->returnData;
        }
        $data = [];
        foreach ($res as $key=>$value){
            $tmp = [];
            foreach ($value as $k=>$v){
                if($k=='nextcontact'){
                    $k = 'contact_next_time';
                }
                if($k=='customer_level'){
                    $k = 'progress';
                }
                if($k=='status'){
                    if($v==0){
                        $v = 1;
                    }else{
                        $v = 0;
                    }
                }
                if($k == "cust_state"){
                    $v = $v == 1 ? 0 : 1;
                }
                $tmp[$k] = $v;
            }
            $data[] = $tmp;
        }
        try {
            //检查是否完成
            foreach ($data as $sqlData){
                DB::table('customer')->insert($sqlData);
            }
            return $this->returnData;
        } catch(\Illuminate\Database\QueryException $e) {
            $errorInfo = json_decode(json_encode($e->errorInfo),true);
            Log::info('table customer update error:',array('errorinfo'=>json_encode($errorInfo)));
            return $this->returnData;
        }
    }

    /* 修复客户数据表 */
    private function _copyMemberData()
    {
        $res = DB::table('members')
            ->select('id','name','mobile','password','email','status','act_time','created_at','updated_at')
            ->where('cust_state',0)
            ->get();
        if(!$res){
            $this->returnData = ErrorCode::$admin_enum['not_exist'];
            return $this->returnData;
        }
        $res = json_decode(json_encode($res),true);
        if(!is_array($res) || count($res)<1){
            $this->returnData = ErrorCode::$admin_enum['not_exist'];
            return $this->returnData;
        }
        $data = [];
        foreach ($res as $key=>$value){
            $tmp = [];
            foreach ($value as $k=>$v){
                if($k=='act_time'){
                    $k = 'active_time';
                }
                if($k=='created_at'){
                    $k = 'create_time';
                }
                if($k=='updated_at'){
                    $k = 'update_time';
                }
                if($k=='status'){
                    if($v==0){
                        $v = 1;
                    }else{
                        $v = 0;
                    }
                }
                $tmp[$k] = $v;
            }
            $data[] = $tmp;
        }
        try {
            //检查是否完成
            foreach ($data as $sqlData){
                DB::table('member')->insert($sqlData);
            }
            return $this->returnData;
        } catch(\Illuminate\Database\QueryException $e) {
            $errorInfo = json_decode(json_encode($e->errorInfo),true);
            Log::info('table member update error:',array('errorinfo'=>json_encode($errorInfo)));
            return $this->returnData;
        }
    }

    /* 修复客户扩展数据表 */
    private function _copyMemberExtendData()
    {
        $res = DB::table('members')
            ->select('id','realname','type','pic','level','recommend','addperson','donation_amount','wallet','position','company','wechat','qq','source','project','remarks','updated_at')
            ->where('cust_state',0)
            ->get();
        if(!$res){
            $this->returnData = ErrorCode::$admin_enum['not_exist'];
            return $this->returnData;
        }
        $res = json_decode(json_encode($res),true);
        if(!is_array($res) || count($res)<1){
            $this->returnData = ErrorCode::$admin_enum['not_exist'];
            return $this->returnData;
        }
        $data = [];
        foreach ($res as $key=>$value){
            $tmp = [];
            foreach ($value as $k=>$v){
                if($k=='id'){
                    $k = 'member_id';
                }
                if($k=='pic'){
                    $k = 'avatar';
                }
                if($k=='donation_amount'){
                    $k = 'cash_coupon';
                }
                if($k=='wallet'){
                    $k = 'balance';
                }
                if($k=='updated_at'){
                    $k = 'update_time';
                }
                $tmp[$k] = $v;
            }
            $data[] = $tmp;
        }
        try {
            //检查是否完成
            foreach ($data as $sqlData){
                DB::table('member_extend')->insert($sqlData);
            }
            return $this->returnData;
        } catch(\Illuminate\Database\QueryException $e) {
            $errorInfo = json_decode(json_encode($e->errorInfo),true);
            Log::info('table member_extend update error:',array('errorinfo'=>json_encode($errorInfo)));
            return $this->returnData;
        }
    }
}
